<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;


class Regulate extends Model
{
    public function get_kaoqin($squad_id,$time_arr){
        //考勤异常情况
        $num = array();
        //当前条件下异常
        $total_pre = DB::table('notices as n')
            ->whereIn('n.notice_type',['1','2','3','4','5'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['request']['end'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['request']['start'])
            ->whereIn('n.squad_id',$squad_id)
            ->whereIn('n.status',['3','4'])
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();

        //当前条件下考勤异常
        $attendance_pre = DB::table('notices as n')
            ->where('n.notice_type','1')
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['request']['end'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['request']['start'])
            ->whereIn('n.squad_id',$squad_id)
            ->whereIn('n.status',['3','4'])
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //当前条件下测试异常
        $test_pre = DB::table('notices as n')
            ->where('n.notice_type','2')
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['request']['end'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['request']['start'])
            ->whereIn('n.squad_id',$squad_id)
            ->whereIn('n.status',['3','4'])
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //当前条件下家校异常
        $jiaxiao_pre = DB::table('notices as n')
            ->where('n.notice_type','3')
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['request']['end'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['request']['start'])
            ->whereIn('n.squad_id',$squad_id)
            ->whereIn('n.status',['3','4'])
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //当前条件下听写异常
        $dictation_pre = DB::table('notices as n')
            ->where('n.notice_type','4')
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['request']['end'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['request']['start'])
            ->whereIn('n.squad_id',$squad_id)
            ->whereIn('n.status',['3','4'])
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //当前条件下作业异常
        $zuoye_pre = DB::table('notices as n')
            ->where('n.notice_type','5')
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['request']['end'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['request']['start'])
            ->whereIn('n.squad_id',$squad_id)
            ->whereIn('n.status',['3','4'])
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();

        //本月累计异常
        $total_month = DB::table('notices as n')
            ->whereIn('n.notice_type',['1','2','3','4','5'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['month']['end'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['month']['start'])
            ->whereIn('n.squad_id',$squad_id)
            ->whereIn('n.status',['3','4'])
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //本月考勤累计异常
        $attendance_month = DB::table('notices as n')
            ->where('n.notice_type','1')
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['month']['end'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['month']['start'])
            ->whereIn('n.squad_id',$squad_id)
            ->whereIn('n.status',['3','4'])
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //本月测试累计异常
        $test_month = DB::table('notices as n')
            ->where('n.notice_type','2')
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['month']['end'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['month']['start'])
            ->whereIn('n.squad_id',$squad_id)
            ->whereIn('n.status',['3','4'])
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //本月家校累计异常
        $jiaxiao_month = DB::table('notices as n')
            ->where('n.notice_type','3')
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['month']['end'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['month']['start'])
            ->whereIn('n.squad_id',$squad_id)
            ->whereIn('n.status',['3','4'])
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //本月听写累计异常
        $dictation_month = DB::table('notices as n')
            ->where('n.notice_type','4')
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['month']['end'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['month']['start'])
            ->whereIn('n.squad_id',$squad_id)
            ->whereIn('n.status',['3','4'])
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //本月作业累计异常
        $zuoye_month = DB::table('notices as n')
            ->where('n.notice_type','5')
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['month']['end'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['month']['start'])
            ->whereIn('n.squad_id',$squad_id)
            ->whereIn('n.status',['3','4'])
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();

        //本周累计异常
        // $total_week = DB::table('notices as n')
        //     ->whereIn('n.notice_type',['1','2','3','4','5'])
        //     ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['week']['end'])
        //     ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['week']['start'])
        //     ->whereIn('n.squad_id',$squad_id)
        //     ->whereIn('n.status',['3','4'])
        //     ->groupBy('n.tutor_id')
        //     ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        // //本周考勤累计异常
        // $attendance_week = DB::table('notices as n')
        //     ->where('n.notice_type','1')
        //     ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['week']['end'])
        //     ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['week']['start'])
        //     ->whereIn('n.squad_id',$squad_id)
        //     ->whereIn('n.status',['3','4'])
        //     ->groupBy('n.tutor_id')
        //     ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        // //本周测试累计异常
        // $test_week = DB::table('notices as n')
        //     ->where('n.notice_type','2')
        //     ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['week']['end'])
        //     ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['week']['start'])
        //     ->whereIn('n.squad_id',$squad_id)
        //     ->whereIn('n.status',['3','4'])
        //     ->groupBy('n.tutor_id')
        //     ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        // //本周家校累计异常
        // $jiaxiao_week = DB::table('notices as n')
        //     ->where('n.notice_type','3')
        //     ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['week']['end'])
        //     ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['week']['start'])
        //     ->whereIn('n.squad_id',$squad_id)
        //     ->whereIn('n.status',['3','4'])
        //     ->groupBy('n.tutor_id')
        //     ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        // //本周听写累计异常
        // $dictation_week = DB::table('notices as n')
        //     ->where('n.notice_type','4')
        //     ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['week']['end'])
        //     ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['week']['start'])
        //     ->whereIn('n.squad_id',$squad_id)
        //     ->whereIn('n.status',['3','4'])
        //     ->groupBy('n.tutor_id')
        //     ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        // //本周作业累计异常
        // $zuoye_week = DB::table('notices as n')
        //     ->where('n.notice_type','5')
        //     ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['week']['end'])
        //     ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['week']['start'])
        //     ->whereIn('n.squad_id',$squad_id)
        //     ->whereIn('n.status',['3','4'])
        //     ->groupBy('n.tutor_id')
        //     ->select('n.tutor_id',DB::raw('count(1) as num'))->get();

        //至今未处理异常
        $total_not = DB::table('notices as n')
            ->whereIn('n.notice_type',['1','2','3','4','5'])
            ->where('n.status','3')
            ->whereIn('n.squad_id',$squad_id)
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //至今未处理考勤异常
        $attendance_not = DB::table('notices as n')
            ->where('n.notice_type','1')
            ->where('n.status','3')
            ->whereIn('n.squad_id',$squad_id)
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //至今未处理测试异常
        $test_not = DB::table('notices as n')
            ->where('n.notice_type','2')
            ->where('n.status','3')
            ->whereIn('n.squad_id',$squad_id)
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //至今未处理家校异常
        $jiaxiao_not = DB::table('notices as n')
            ->where('n.notice_type','3')
            ->where('n.status','3')
            ->whereIn('n.squad_id',$squad_id)
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //至今未处理听写异常
        $dictation_not = DB::table('notices as n')
            ->where('n.notice_type','4')
            ->where('n.status','3')
            ->whereIn('n.squad_id',$squad_id)
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();
        //至今未处理作业异常
        $zuoye_not = DB::table('notices as n')
            ->where('n.notice_type','5')
            ->where('n.status','3')
            ->whereIn('n.squad_id',$squad_id)
            ->groupBy('n.tutor_id')
            ->select('n.tutor_id',DB::raw('count(1) as num'))->get();

        foreach($total_pre as $v){
            if(!isset($num[$v->tutor_id]['total_pre'])){
                $num[$v->tutor_id]['total_pre'] = 0;
            }
            $num[$v->tutor_id]['total_pre'] = $v->num;
        }
        foreach($attendance_pre as $v){
            if(!isset($num[$v->tutor_id]['attendance_pre'])){
                $num[$v->tutor_id]['attendance_pre'] = 0;
            }
            $num[$v->tutor_id]['attendance_pre'] = $v->num;
        }
        foreach($test_pre as $v){
            if(!isset($num[$v->tutor_id]['test_pre'])){
                $num[$v->tutor_id]['test_pre'] = 0;
            }
            $num[$v->tutor_id]['test_pre'] = $v->num;
        }
        foreach($jiaxiao_pre as $v){
            if(!isset($num[$v->tutor_id]['jiaxiao_pre'])){
                $num[$v->tutor_id]['jiaxiao_pre'] = 0;
            }
            $num[$v->tutor_id]['jiaxiao_pre'] = $v->num;
        }
        foreach($dictation_pre as $v){
            if(!isset($num[$v->tutor_id]['dictation_pre'])){
                $num[$v->tutor_id]['dictation_pre'] = 0;
            }
            $num[$v->tutor_id]['dictation_pre'] = $v->num;
        }
        foreach($zuoye_pre as $v){
            if(!isset($num[$v->tutor_id]['zuoye_pre'])){
                $num[$v->tutor_id]['zuoye_pre'] = 0;
            }
            $num[$v->tutor_id]['zuoye_pre'] = $v->num;
        }
        
        foreach($total_month as $v){
            if(!isset($num[$v->tutor_id]['total_month'])){
                $num[$v->tutor_id]['total_month'] = 0;
            }
            $num[$v->tutor_id]['total_month'] = $v->num;
        }
        foreach($attendance_month as $v){
            if(!isset($num[$v->tutor_id]['attendance_month'])){
                $num[$v->tutor_id]['attendance_month'] = 0;
            }
            $num[$v->tutor_id]['attendance_month'] = $v->num;
        }
        foreach($test_month as $v){
            if(!isset($num[$v->tutor_id]['test_month'])){
                $num[$v->tutor_id]['test_month'] = 0;
            }
            $num[$v->tutor_id]['test_month'] = $v->num;
        }
        foreach($jiaxiao_month as $v){
            if(!isset($num[$v->tutor_id]['jiaxiao_month'])){
                $num[$v->tutor_id]['jiaxiao_month'] = 0;
            }
            $num[$v->tutor_id]['jiaxiao_month'] = $v->num;
        }
        foreach($dictation_month as $v){
            if(!isset($num[$v->tutor_id]['dictation_month'])){
                $num[$v->tutor_id]['dictation_month'] = 0;
            }
            $num[$v->tutor_id]['dictation_month'] = $v->num;
        }
        foreach($zuoye_month as $v){
            if(!isset($num[$v->tutor_id]['zuoye_month'])){
                $num[$v->tutor_id]['zuoye_month'] = 0;
            }
            $num[$v->tutor_id]['zuoye_month'] = $v->num;
        }
        // foreach($total_week as $v){
        //     if(!isset($num[$v->tutor_id]['total_week'])){
        //         $num[$v->tutor_id]['total_week'] = 0;
        //     }
        //     $num[$v->tutor_id]['total_week'] = $v->num;
        // }
        // foreach($attendance_week as $v){
        //     if(!isset($num[$v->tutor_id]['attendance_week'])){
        //         $num[$v->tutor_id]['attendance_week'] = 0;
        //     }
        //     $num[$v->tutor_id]['attendance_week'] = $v->num;
        // }
        // foreach($test_week as $v){
        //     if(!isset($num[$v->tutor_id]['test_week'])){
        //         $num[$v->tutor_id]['test_week'] = 0;
        //     }
        //     $num[$v->tutor_id]['test_week'] = $v->num;
        // }
        // foreach($jiaxiao_week as $v){
        //     if(!isset($num[$v->tutor_id]['jiaxiao_week'])){
        //         $num[$v->tutor_id]['jiaxiao_week'] = 0;
        //     }
        //     $num[$v->tutor_id]['jiaxiao_week'] = $v->num;
        // }
        // foreach($dictation_week as $v){
        //     if(!isset($num[$v->tutor_id]['dictation_week'])){
        //         $num[$v->tutor_id]['dictation_week'] = 0;
        //     }
        //     $num[$v->tutor_id]['dictation_week'] = $v->num;
        // }
        // foreach($zuoye_week as $v){
        //     if(!isset($num[$v->tutor_id]['zuoye_week'])){
        //         $num[$v->tutor_id]['zuoye_week'] = 0;
        //     }
        //     $num[$v->tutor_id]['zuoye_week'] = $v->num;
        // }
        foreach($total_not as $v){
            if(!isset($num[$v->tutor_id]['total_not'])){
                $num[$v->tutor_id]['total_not'] = 0;
            }
            $num[$v->tutor_id]['total_not'] = $v->num;
        }
        foreach($attendance_not as $v){
            if(!isset($num[$v->tutor_id]['attendance_not'])){
                $num[$v->tutor_id]['attendance_not'] = 0;
            }
            $num[$v->tutor_id]['attendance_not'] = $v->num;
        }
        foreach($test_not as $v){
            if(!isset($num[$v->tutor_id]['test_not'])){
                $num[$v->tutor_id]['test_not'] = 0;
            }
            $num[$v->tutor_id]['test_not'] = $v->num;
        }
        foreach($jiaxiao_not as $v){
            if(!isset($num[$v->tutor_id]['jiaxiao_not'])){
                $num[$v->tutor_id]['jiaxiao_not'] = 0;
            }
            $num[$v->tutor_id]['jiaxiao_not'] = $v->num;
        }
        foreach($dictation_not as $v){
            if(!isset($num[$v->tutor_id]['dictation_not'])){
                $num[$v->tutor_id]['dictation_not'] = 0;
            }
            $num[$v->tutor_id]['dictation_not'] = $v->num;
        }
        foreach($zuoye_not as $v){
            if(!isset($num[$v->tutor_id]['zuoye_not'])){
                $num[$v->tutor_id]['zuoye_not'] = 0;
            }
            $num[$v->tutor_id]['zuoye_not'] = $v->num;
        }

        return $num;
    }

    // public function get_test($squad_id,$time_arr){
    //     //考勤异常情况
    //     $test = $test_m = $test_w = $test_n = array();

    //     $num = array();
    //     //当前条件下异常
    //     $test = DB::table('notices as n')->join('test_assigns as ta','n.test_assign_id','=','ta.id')
    //         ->join('squads as s','ta.squad_id','=','s.id')
    //         ->where('n.notice_type','=','2')
    //         ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['request']['end'])
    //         ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['request']['start'])
    //         ->whereIn('ta.squad_id',$squad_id)
    //         ->whereIn('n.status',['3','4'])
    //         ->groupBy('s.class_teacher')
    //         ->select('s.class_teacher',DB::raw('count(1) as num'))->get();

    //     //本月累计异常
    //     $test_m = DB::table('notices as n')->join('test_assigns as ta','n.test_assign_id','=','ta.id')
    //         ->join('squads as s','ta.squad_id','=','s.id')
    //         ->where('n.notice_type','=','2')
    //         ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['month']['end'])
    //         ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['month']['start'])
    //         ->whereIn('ta.squad_id',$squad_id)
    //         ->whereIn('n.status',['3','4'])
    //         ->groupBy('s.class_teacher')
    //         ->select('s.class_teacher',DB::raw('count(1) as num'))->get();

    //     //周累计异常
    //     $test_w = DB::table('notices as n')->join('test_assigns as ta','n.test_assign_id','=','ta.id')
    //         ->join('squads as s','ta.squad_id','=','s.id')
    //         ->where('n.notice_type','=','2')
    //         ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.$time_arr['week']['end'])
    //         ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.$time_arr['week']['start'])
    //         ->whereIn('ta.squad_id',$squad_id)
    //         ->whereIn('n.status',['3','4'])
    //         ->groupBy('s.class_teacher')
    //         ->select('s.class_teacher',DB::raw('count(1) as num'))->get();

    //     //至今未处理异常
    //     $test_n = DB::table('notices as n')->join('test_assigns as ta','n.test_assign_id','=','ta.id')
    //         ->join('squads as s','ta.squad_id','=','s.id')
    //         ->where('n.notice_type','=','2')
    //         ->where('n.status','=','3')
    //         ->whereIn('ta.squad_id',$squad_id)
    //         ->groupBy('s.class_teacher')
    //         ->select('s.class_teacher',DB::raw('count(1) as num'))->get();

    //     foreach($test as $v){
    //         if(!isset($num[$v->class_teacher]['total_pre'])){
    //             $num[$v->class_teacher]['total_pre'] = 0;
    //         }
    //         $num[$v->class_teacher]['total_pre'] += $v->num;
    //     }
    //     foreach($test_m as $v){
    //         if(!isset($num[$v->class_teacher]['total_month'])){
    //             $num[$v->class_teacher]['total_month'] = 0;
    //         }
    //         $num[$v->class_teacher]['total_month'] += $v->num;
    //     }
    //     foreach($test_w as $v){
    //         if(!isset($num[$v->class_teacher]['total_week'])){
    //             $num[$v->class_teacher]['total_week'] = 0;
    //         }
    //         $num[$v->class_teacher]['total_week'] += $v->num;
    //     }
    //     foreach($test_n as $v){
    //         if(!isset($num[$v->class_teacher]['total_not'])){
    //             $num[$v->class_teacher]['total_not'] = 0;
    //         }
    //         $num[$v->class_teacher]['total_not'] += $v->num;
    //     }

    //     return $num;
    // }
}
